﻿---------------PHANTOM---------------
Create proc [dbo].[usp_Soluongchuyen]
@chuyen nchar(20),
@ngaybd varchar(20),
@ngaykt varchar(20),
@tong int output
As
begin tran
set tran isolation level serializable
begin try
	if(not exists( select * from LICHDI where MACHUYEN=@chuyen))
	begin
		print N'Khong ton tai chuyen xe co ma la '+@chuyen
		return
	end
		set tran isolation level serializable
		set @tong= (select count(MACHUYEN) from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt)
		waitfor delay '00:00:10'
		select * from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt
	 
end try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 
commit
------------------Unrepeatable read-----------------
Create proc [dbo].[usp_tracuutheobendi]
@bendi nvarchar(50),
@tong int output
as
begin tran
	--set tran isolation level serializable
	set tran isolation level repeatable read
	if(not exists(Select ld.MACHUYEN from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDI=@bendi))
	begin
		print N'Khong co chuyen xe nao bat dau tu' +' '+ @bendi
		rollback tran
		return
	end
	set @tong=(select count(ld.MACHUYEN) from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDI=@bendi)
	waitfor delay '00:00:05'
	select ld.NGAYDI,cx.GIODI,cx.GIODEN,cx.BENXEDI,cx.BENXEDEN,ld.MALOAI,ld.SOHIEU,lx.TRONGTAI,ld.TTHIENTAI 
	from LICHDI ld, CHUYENXE cx, LOAIXE lx 
	where cx.MACHUYEN=ld.MACHUYEN and ld.MALOAI=lx.MALOAI and cx.BENXEDI=@bendi
commit
------------------Dirty Read-----------------------
Create proc usp_Dangkychuyendi
@ngay Datetime
as
begin tran
	set tran isolation level read committed
	begin try
	   select * from DangKy where NgayDi = @ngay
	end try
	begin catch
		declare @error varchar(1000)
		set @error = N'Lỗi: ' + error_message()
		raiserror (@error,16,1)
		rollback tran
		return
	end catch
commit tran

------------------Lost Update------------------------
Create proc usp_giamhanghoa1  /*Thong tin khach hang*/@maKH char(10)
,/*Thong tin dat cho*/@maChuyen char(10),@khoiLuong int, @ngayDi varchar(20), @soHieu char(10) 
as
begin
	set tran isolation level Serializable
	--set tran isolation level READ UNCOMMITTED
	
	-- kiem tra khach hang co ton tai hay khong ?
		if(not exists(select * from KHACHHANG kh where kh.MAKH=@maKH))
		begin
			
			RAISERROR(N'Khách hành không tồn tại',16,1)
			return
		end
	-- khach hang co dat cho hay khong
		if( not exists(select * from KHACHHANG  join DATCHO on KHACHHANG.MAKH=DATCHO.MAKH and KHACHHANG.MAKH=@maKH 
												join LICHDI on LICHDI.NGAYDI=DATCHO.NGAYDI and LICHDI.MACHUYEN=DATCHO.MACHUYEN and LICHDI.MACHUYEN =@maChuyen and LICHDI.SOHIEU=@soHieu and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi))
		begin
			--print(N'Khách hành chưa đăng ký chuyển hàng')
			RAISERROR(N'Khách hành chưa đăng ký chuyển hàng',16,1)
			---rollback tran
			return
		end
	-- kiem tra vuot qua trong tai lon nhat cua xe
	--The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION : It looks like your transaction failed, got rolled back and there is nothing to commit
	begin try
	begin tran a
		declare @tt int
		set @tt=(select LICHDI.TTHIENTAI from KHACHHANG  join DATCHO with(xlock) on KHACHHANG.MAKH=DATCHO.MAKH and KHACHHANG.MAKH=@maKH 
												join LICHDI with(xlock) on LICHDI.NGAYDI=DATCHO.NGAYDI and LICHDI.MACHUYEN=DATCHO.MACHUYEN and LICHDI.MACHUYEN =@maChuyen and LICHDI.SOHIEU=@soHieu and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi)
		--if(@tt-@khoiLuong<0)
		--begin
		--	print(N'Trong tai xe khong duoc am')
		--	rollback tran
		--	return
		--end
	-- cap nhat khoi luong hang
		waitfor delay '00:00:10'
		set @tt=@tt+@khoiLuong
		update LICHDI set TTHIENTAI=@tt where MACHUYEN=@maChuyen and CONVERT(varchar(20),LICHDI.NGAYDI,103)=@ngayDi and LICHDI.SOHIEU=@soHieu
		update DATCHO set SOLUONG=SOLUONG+@khoiLuong where DATCHO.MAKH=@maKH and CONVERT(varchar(20),DATCHO.NGAYDI,103)=@ngayDi and DATCHO.MACHUYEN=@maChuyen and DATCHO.SOHIEU=@soHieu
		commit tran a
	end try
	begin catch
		rollback tran a
		DECLARE @ErrorMessage varchar(500)
		SET @ErrorMessage = ERROR_MESSAGE()
		IF ERROR_NUMBER() = 1205
		begin
			raiserror(@ErrorMessage, 16,1)
		end
	end catch
end